package visitor.outputvisitor

import ast.SqlNode
import ast.statement.ddl.SqlCreateIndex
import ast.statement.ddl.SqlCreateTable
import ast.statement.ddl.SqlDropIndex
import ast.statement.ddl.SqlDropTable
import ast.statement.delete.SqlDelete
import ast.expr.*
import ast.statement.insert.SqlInsert
import ast.limit.SqlLimit
import ast.order.SqlOrderBy
import ast.statement.SqlStatement
import ast.statement.select.*
import ast.table.SqlIdentifierTableSource
import ast.table.SqlJoinTableSource
import ast.table.SqlSubQueryTableSource
import ast.table.SqlTableSource
import ast.statement.truncate.SqlTruncate
import ast.statement.update.SqlUpdate
import ast.statement.upsert.SqlUpsert
import java.sql.SQLException

abstract class SqlOutPutVisitor {
    val sqlBuilder = StringBuilder()

    open val quote = "\""

    fun sql() = sqlBuilder.toString()

    fun visitSqlStatement(sqlNode: SqlStatement) {
        when (sqlNode) {
            is SqlSelectQuery -> visitSqlSelectQuery(sqlNode)

            is SqlDelete -> visitSqlDelete(sqlNode)

            is SqlUpdate -> visitSqlUpdate(sqlNode)

            is SqlInsert -> visitSqlInsert(sqlNode)

            is SqlUpsert -> visitSqlUpsert(sqlNode)

            is SqlTruncate -> visitSqlTruncate(sqlNode)

            is SqlCreateIndex -> visitSqlCreateIndex(sqlNode)

            is SqlCreateTable -> visitSqlCreateTable(sqlNode)

            is SqlDropIndex -> visitSqlDropIndex(sqlNode)

            is SqlDropTable -> visitSqlDropTable(sqlNode)
        }
    }

    open fun visitSqlSelectQuery(sqlSelectQuery: SqlSelectQuery) {
        when (sqlSelectQuery) {
            is SqlSelect -> visitSqlSelect(sqlSelectQuery)

            is SqlUnionSelect -> {
                visitSqlSelectQuery(sqlSelectQuery.left)

                sqlBuilder.append("\n${sqlSelectQuery.type.type}\n")

                visitSqlSelectQuery(sqlSelectQuery.right)
            }

            is SqlWithSelect -> visitSqlWithSelect(sqlSelectQuery)

            is SqlValuesSelect -> visitSqlValuesSelect(sqlSelectQuery)
        }
    }

    open fun visitSqlSelect(sqlSelect: SqlSelect) {
        sqlBuilder.append("SELECT ")

        if (sqlSelect.selectList.isEmpty()) {
            throw SQLException("SELECT列表为空")
        }

        if (sqlSelect.distinct) {
            sqlBuilder.append("DISTINCT ")
        }

        printList(sqlSelect.selectList, ::visitSqlSelectItem)

        sqlSelect.from?.let {
            sqlBuilder.append(" FROM ")
            visitSqlTableSource(it)
        }

        sqlSelect.where?.let {
            sqlBuilder.append(" WHERE ")
            visitSqlExpr(it)
        }

        if (sqlSelect.groupBy.isNotEmpty()) {
            sqlBuilder.append(" GROUP BY ")
            printList(sqlSelect.groupBy, ::visitSqlExpr)
        }


        sqlSelect.having?.let {
            sqlBuilder.append(" HAVING ")
            visitSqlExpr(it)
        }

        if (sqlSelect.orderBy.isNotEmpty()) {
            sqlBuilder.append(" ORDER BY ")
            printList(sqlSelect.orderBy, ::visitSqlOrderBy)
        }

        sqlSelect.limit?.let {
            sqlBuilder.append(" ")
            visitSqlLimit(it)
        }

        if (sqlSelect.forUpdate) {
            sqlBuilder.append(" ")
            visitSqlForUpdate()
        }
    }

    open fun visitSqlSelectItem(sqlSelectItem: SqlSelectItem) {
        visitSqlExpr(sqlSelectItem.expr)
        sqlSelectItem.alias?.let { sqlBuilder.append(" AS $quote${sqlSelectItem.alias}$quote") }
    }

    open fun visitSqlDelete(sqlDelete: SqlDelete) {
        sqlBuilder.append("DELETE FROM ")
        visitSqlExpr(sqlDelete.table!!)

        sqlDelete.where?.let {
            sqlBuilder.append(" WHERE ")
            visitSqlExpr(it)
        }
    }

    open fun visitSqlWithSelect(sqlWithSelect: SqlWithSelect) {
        sqlBuilder.append("WITH ")
        if (sqlWithSelect.recursive) {
            sqlBuilder.append("RECURSIVE ")
        }

        fun visitSqlWithItem(sqlWithItem: SqlWithItem) {
            visitSqlExpr(sqlWithItem.name)
            if (sqlWithItem.columns.isNotEmpty()) {
                sqlBuilder.append("(")
                printList(sqlWithItem.columns, ::visitSqlExpr)
                sqlBuilder.append(")")
            }
            sqlBuilder.append(" AS ")
            sqlBuilder.append("(")
            visitSqlSelectQuery(sqlWithItem.query)
            sqlBuilder.append(")")
            sqlBuilder.append("\n")
        }

        printList(sqlWithSelect.with, ::visitSqlWithItem)

        visitSqlSelectQuery(sqlWithSelect.query!!)
    }

    open fun visitSqlValuesSelect(sqlValuesSelect: SqlValuesSelect) {
        sqlBuilder.append("VALUES ")
        printList(sqlValuesSelect.values.map { SqlListExpr(it) }, ::visitSqlExpr)
    }

    open fun visitSqlUpdate(sqlUpdate: SqlUpdate) {
        sqlBuilder.append("UPDATE ")
        visitSqlExpr(sqlUpdate.table!!)
        sqlBuilder.append(" SET ")
        sqlUpdate.setList.forEachIndexed { index, item ->
            visitSqlExpr(item.first)
            sqlBuilder.append(" = ")
            visitSqlExpr(item.second)
            if (index < sqlUpdate.setList.size - 1) {
                sqlBuilder.append(",")
                sqlBuilder.append(" ")
            }
        }
        sqlUpdate.where?.let {
            sqlBuilder.append(" WHERE ")
            visitSqlExpr(it)
        }
    }

    open fun visitSqlInsert(sqlInsert: SqlInsert) {
        sqlBuilder.append("INSERT INTO ")
        visitSqlExpr(sqlInsert.table!!)
        if (sqlInsert.columns.isNotEmpty()) {
            sqlBuilder.append(" (")
            printList(sqlInsert.columns, ::visitSqlExpr)
            sqlBuilder.append(")")
        }
        if (sqlInsert.query != null) {
            sqlBuilder.append(" ")
            visitSqlSelectQuery(sqlInsert.query!!)
        } else {
            sqlBuilder.append(" VALUES ")
            printList(sqlInsert.values.map { SqlListExpr(it) }, ::visitSqlExpr)
        }
    }

    open fun visitSqlUpsert(sqlUpsert: SqlUpsert) {}

    open fun visitSqlTruncate(sqlTruncate: SqlTruncate) {
        sqlBuilder.append("TRUNCATE ")
        visitSqlExpr(sqlTruncate.table!!)
    }

    open fun visitSqlCreateIndex(sqlCreateIndex: SqlCreateIndex) {
        sqlBuilder.append("CREATE ")
        sqlCreateIndex.type?.let {
            sqlBuilder.append("$it ")
        }
        sqlBuilder.append("INDEX ")
        visitSqlExpr(sqlCreateIndex.name!!)
        sqlBuilder.append(" ON ")
        visitSqlExpr(sqlCreateIndex.table!!)
        sqlBuilder.append(" (")
        printList(sqlCreateIndex.columns, ::visitSqlExpr)
        sqlBuilder.append(")")
    }

    open fun visitSqlCreateTable(sqlCreateTable: SqlCreateTable) {
        sqlBuilder.append("CREATE TABLE ")
        visitSqlExpr(sqlCreateTable.name!!)
        sqlBuilder.append(" (")

        printList(sqlCreateTable.columnList) {
            visitSqlExpr(it.name!!)
            sqlBuilder.append(" ")
            sqlBuilder.append(it.dataType!!)
            if (it.notNull) {
                sqlBuilder.append(" NOT NULL")
            }
            it.default?.let { default ->
                sqlBuilder.append(" DEFAULT ")
                visitSqlExpr(default)
            }
            if (it.primary) {
                sqlBuilder.append(" PRIMARY KEY")
            }
        }

        sqlBuilder.append(")")
    }

    open fun visitSqlDropIndex(sqlDropIndex: SqlDropIndex) {
        sqlBuilder.append("DROP INDEX ")
        visitSqlExpr(sqlDropIndex.name!!)
        sqlDropIndex.table?.let {
            sqlBuilder.append(" ON ")
            visitSqlExpr(it)
        }
    }

    open fun visitSqlDropTable(sqlDropTable: SqlDropTable) {
        sqlBuilder.append("DROP TABLE ")
        visitSqlExpr(sqlDropTable.table!!)
    }

    abstract fun visitSqlLimit(sqlLimit: SqlLimit)

    open fun visitSqlOrderBy(sqlOrderBy: SqlOrderBy) {
        visitSqlExpr(sqlOrderBy.expr)
        sqlBuilder.append(" ${sqlOrderBy.order.order}")
    }

    open fun visitSqlForUpdate() {
        sqlBuilder.append("FOR UPDATE")
    }

    open fun visitSqlBinaryExpr(sqlExpr: SqlBinaryExpr) {
        fun needParentheses(parent: SqlBinaryExpr, child: SqlExpr): Boolean {
            if (parent.operator == SqlBinaryOperator.AND) {
                if (child is SqlBinaryExpr) {
                    if (child.operator in listOf(SqlBinaryOperator.OR, SqlBinaryOperator.XOR)) {
                        return true
                    }
                }
            }

            if (parent.operator in listOf(SqlBinaryOperator.MUL, SqlBinaryOperator.DIV, SqlBinaryOperator.MOD)) {
                if (child is SqlBinaryExpr) {
                    if (child.operator in listOf(SqlBinaryOperator.ADD, SqlBinaryOperator.SUB)) {
                        return true
                    }
                }
            }

            return false
        }

        val visitLeft = needParentheses(sqlExpr, sqlExpr.left)
        if (visitLeft) {
            sqlBuilder.append("(")
            visitSqlExpr(sqlExpr.left)
            sqlBuilder.append(")")
        } else {
            visitSqlExpr(sqlExpr.left)
        }

        sqlBuilder.append(" ${sqlExpr.operator.operator} ")

        val visitRight = needParentheses(sqlExpr, sqlExpr.right)
        if (visitRight) {
            sqlBuilder.append("(")
            visitSqlExpr(sqlExpr.right)
            sqlBuilder.append(")")
        } else {
            visitSqlExpr(sqlExpr.right)
        }
    }

    open fun visitSqlExpr(sqlExpr: SqlExpr) {
        when (sqlExpr) {
            is SqlBinaryExpr -> {
                visitSqlBinaryExpr(sqlExpr)
            }

            is SqlCharExpr -> {
                sqlBuilder.append(sqlExpr.toString())
            }

            is SqlNumberExpr -> sqlBuilder.append(sqlExpr.number.toString())

            is SqlDecimalExpr -> sqlBuilder.append(sqlExpr.number.toString())

            is SqlBooleanExpr -> sqlBuilder.append(sqlExpr.boolean.toString())

            is SqlDataExpr -> {
                sqlBuilder.append(sqlExpr.toString())
            }

            is SqlIdentifierExpr -> sqlBuilder.append("$quote${sqlExpr.name}$quote")

            is SqlPropertyExpr -> sqlBuilder.append("$quote${sqlExpr.owner}$quote.$quote${sqlExpr.name}$quote")

            is SqlNullExpr -> sqlBuilder.append("NULL")

            is SqlAllColumnExpr -> {
                sqlExpr.owner?.let {
                    sqlBuilder.append("$quote${sqlExpr.owner}$quote.")
                }
                sqlBuilder.append("*")
            }

            is SqlListExpr<*> -> {
                sqlBuilder.append("(")
                printList(sqlExpr.items, ::visitSqlExpr)
                sqlBuilder.append(")")
            }

            is SqlInExpr -> {
                visitSqlExpr(sqlExpr.expr)
                if (sqlExpr.isNot) {
                    sqlBuilder.append(" NOT")
                }
                sqlBuilder.append(" IN ")
                visitSqlExpr(sqlExpr.inExpr)
            }

            is SqlBetweenExpr<*> -> {
                visitSqlExpr(sqlExpr.expr)
                if (sqlExpr.isNot) {
                    sqlBuilder.append(" NOT")
                }
                sqlBuilder.append(" BETWEEN ")
                visitSqlExpr(sqlExpr.start)
                sqlBuilder.append(" AND ")
                visitSqlExpr(sqlExpr.end)
            }

            is SqlCastExpr -> {
                sqlBuilder.append("CAST(")
                visitSqlExpr(sqlExpr.expr)
                sqlBuilder.append(" AS ${sqlExpr.type})")
            }

            is SqlExprFunctionExpr -> {
                sqlBuilder.append(sqlExpr.name)
                sqlBuilder.append("(")
                printList(sqlExpr.args, ::visitSqlExpr)
                sqlBuilder.append(")")
            }

            is SqlSelectQueryExpr -> {
                sqlBuilder.append("(")
                visitSqlSelectQuery(sqlExpr.query)
                sqlBuilder.append(")")
            }

            is SqlAggFunctionExpr -> visitSqlAggFunctionExpr(sqlExpr)

            is SqlOverExpr -> {
                visitSqlAggFunctionExpr(sqlExpr.aggFun)
                sqlBuilder.append(" OVER (")
                if (sqlExpr.partitionBy.isNotEmpty()) {
                    sqlBuilder.append("PARTITION BY ")
                    printList(sqlExpr.partitionBy, ::visitSqlExpr)
                }
                if (sqlExpr.orderBy.isNotEmpty()) {
                    if (sqlExpr.partitionBy.isNotEmpty()) {
                        sqlBuilder.append(" ")
                    }
                    sqlBuilder.append("ORDER BY ")
                    printList(sqlExpr.orderBy, ::visitSqlOrderBy)
                }
                sqlBuilder.append(")")
            }

            is SqlCaseExpr -> {
                sqlBuilder.append("CASE")
                sqlExpr.caseList.forEach {
                    sqlBuilder.append(" WHEN ")
                    visitSqlExpr(it.expr)
                    sqlBuilder.append(" THEN ")
                    visitSqlExpr(it.then)
                }
                sqlBuilder.append(" ELSE ")
                visitSqlExpr(sqlExpr.default)
                sqlBuilder.append(" END")
            }

            is SqlSubQueryPredicateExpr -> {
                sqlBuilder.append(sqlExpr.predicate.predicate)
                sqlBuilder.append(" ")
                visitSqlExpr(sqlExpr.select)
            }
        }
    }

    open fun visitSqlAggFunctionExpr(sqlAggFunctionExpr: SqlAggFunctionExpr) {
        sqlBuilder.append(sqlAggFunctionExpr.name)
        sqlBuilder.append("(")
        if (sqlAggFunctionExpr.distinct) {
            sqlBuilder.append("DISTINCT ")
        }
        if (sqlAggFunctionExpr.name.toUpperCase() == "COUNT" && sqlAggFunctionExpr.args.isEmpty()) {
            sqlBuilder.append("*")
        }
        printList(sqlAggFunctionExpr.args, ::visitSqlExpr)
        if (sqlAggFunctionExpr.orderBy.isNotEmpty()) {
            sqlBuilder.append(" ORDER BY ")
            printList(sqlAggFunctionExpr.orderBy, ::visitSqlOrderBy)
        }
        sqlAggFunctionExpr.attributes.forEach { (k, v) ->
            sqlBuilder.append(" $k ")
            visitSqlExpr(v)
        }
        sqlBuilder.append(")")
    }

    open fun visitSqlTableSource(sqlTableSource: SqlTableSource) {
        when (sqlTableSource) {
            is SqlIdentifierTableSource -> sqlBuilder.append("$quote${sqlTableSource.tableName}$quote")
            is SqlSubQueryTableSource -> {
                if (sqlTableSource.isLateral) {
                    sqlBuilder.append("LATERAL ")
                }
                sqlBuilder.append("(")
                visitSqlSelectQuery(sqlTableSource.select)
                sqlBuilder.append(")")
            }
            is SqlJoinTableSource -> {
                visitSqlTableSource(sqlTableSource.left)
                sqlBuilder.append(" ${sqlTableSource.joinType.type} ")
                visitSqlTableSource(sqlTableSource.right)
                sqlBuilder.append(" ON ")
                visitSqlExpr(sqlTableSource.on!!)
            }
        }

        sqlTableSource.alias?.let {
            sqlBuilder.append(" $it")
            if (sqlTableSource.columnAliasNames.isNotEmpty()) {
                sqlBuilder.append("(")
                printList(sqlTableSource.columnAliasNames.map { SqlIdentifierExpr(it) }) { column->
                    visitSqlExpr(column)
                }
                sqlBuilder.append(")")
            }
        }
    }

    protected inline fun <T : SqlNode> printList(list: List<T>, handle: (T) -> Unit) {
        list.forEachIndexed { index, item ->
            handle(item)
            if (index < list.size - 1) {
                sqlBuilder.append(",")
                sqlBuilder.append(" ")
            }
        }
    }
}